# 数据分析题目解答(建议先赞后看，养成习惯 如果不赞，先拉出去枪毙两分钟 作者：小匠IT)
import pandas as pd
import matplotlib.pyplot as plt
from matplotlib.font_manager import FontProperties
import os
from openpyxl import load_workbook
from openpyxl.drawing.image import Image
from datetime import datetime, timedelta

# 设置输出文件夹路径
output_folder = r'output/23'
os.makedirs(output_folder, exist_ok=True)

# 加载数据（请替换为实际文件路径）
file_path = r'data/23/企业客户忠诚度分析-原始数据.xlsx'

# 读取客户信息表
df_customers = pd.read_excel(file_path)

# 设置中文字体
font_path = r'fonts/SIMSUN.TTC'  # 请根据实际路径调整
font_prop = FontProperties(fname=font_path)

# 假设有一个活动条件列名为“是否满足活动条件”，并且值为True表示满足条件
# 如果没有这样的列，请根据实际情况调整条件筛选逻辑
if '是否满足活动条件' in df_customers.columns:
    qualified_customers = df_customers[df_customers['是否满足活动条件'] == True]
    num_qualified_customers = len(qualified_customers)
    print(f"满足该网店活动条件的客户共有 {num_qualified_customers} 个。")
else:
    print("未找到活动条件列，请确认数据表结构。")

# 第二题：近4个月购买次数最多的客户
# 假设有“订单日期”列，且格式为YYYY-MM-DD
# 计算近4个月的时间范围
end_date = datetime.now()
start_date = end_date - timedelta(days=120)  # 近4个月大约是120天

# 筛选出近4个月的数据
df_recent = df_customers[pd.to_datetime(df_customers['订单日期']) >= start_date]

# 统计每个客户的购买次数
purchase_counts = df_recent['买家会员名'].value_counts()

# 找出购买次数最多的客户
top_customer = purchase_counts.idxmax()
top_purchase_count = purchase_counts.max()
print(f"近4个月，该网店购买次数最多的客户是 {top_customer}，其购买次数是 {top_purchase_count}。")

# 第三题：插入数据透视表并筛选出大于等于4次购买频次的客户
pivot_table_freq = pd.pivot_table(df_customers, values='订单状态', index='买家会员名', aggfunc='count')
pivot_table_freq.columns = ['购买频次']

high_frequency_customers = pivot_table_freq[pivot_table_freq['购买频次'] >= 4]

plt.figure(figsize=(14, 7))
high_frequency_customers.plot(kind='bar', legend=False)
plt.xlabel('买家会员名', fontproperties=font_prop)
plt.ylabel('购买频次', fontproperties=font_prop)
plt.title('高频客户购买频次分析', fontproperties=font_prop)
plt.xticks(fontproperties=font_prop, rotation=45)
plt.tight_layout()

output_image_path_high_freq = os.path.join(output_folder, 'high_frequency_customers_distribution.png')
plt.savefig(output_image_path_high_freq, dpi=300, bbox_inches='tight')
plt.close()

output_excel_path_high_freq = os.path.join(output_folder, '高频客户分析.xlsx')
with pd.ExcelWriter(output_excel_path_high_freq, engine='openpyxl') as writer:
    high_frequency_customers.to_excel(writer, sheet_name='高频客户')

    workbook = writer.book
    worksheet = writer.sheets['高频客户']
    
    img = Image(output_image_path_high_freq)
    worksheet.add_image(img, 'E2')

print("高频客户分析已完成，结果已保存到", output_excel_path_high_freq)

# 第四题：简答题
loyalty_programs = """
网店回馈忠诚客户的形式有：
1. 积分奖励：客户每次购物可以获得积分，积分可以兑换礼品或折扣。
2. VIP专属优惠：提供VIP会员专享的折扣、优先配送等特权。
"""

print(loyalty_programs)

# 将所有结果汇总到一个文本文件中
summary_text_path = os.path.join(output_folder, '分析结论.txt')
with open(summary_text_path, 'w', encoding='utf-8') as f:
    f.write(f"满足该网店活动条件的客户共有 {num_qualified_customers} 个。\n")
    f.write(f"近4个月，该网店购买次数最多的客户是 {top_customer}，其购买次数是 {top_purchase_count}。\n")
    f.write(f"高频客户分析结果已保存到 {output_excel_path_high_freq}。\n")
    f.write(loyalty_programs)

print("所有分析已完成，结果已汇总保存到", summary_text_path)